use master
go
create database ATM
on
(
name='ATM',
filename='D:\UFO\ATM.mdf',
size=5mb,
maxsize=50mb,
filegrowth=15%
)
log on
(
name='ATM_log',
filename='D:\UFO\ATM_log.ldf',
size=5mb,
maxsize=50mb,
filegrowth=15%
)
use ATM
go
create table userInfo 
(
customerID int primary key identity(1,1),
customerName nvarchar(20) not null,
PID char(18) unique check(len(PID) in(15,18)) not null,
telephone char(13) not null check(len(telephone)=13 or telephone like '____-________'),
address nvarchar(50)
)
create table cardInfo
(
cardID nvarchar(18) primary key not null check(substring(cardID,1,9)='1010 3576' and len(cardID)=18),
curType char(10) default('RMB') not null,
savingType nvarchar(4) check(savingType in('','','')),
openDate date default(getdate()) not null,
balance money not null,
pass int check(len(pass)=6) default(888888) not null,
IsReportLoss nvarchar(1) check(IsReportLoss in('','')) default('') not null,
customerID int constraint FK_userInfo_customerID references userInfo(customerID) not null
)
create table transInfo
(
transId int primary key identity,
transDate date not null default(getdate()),
cardID nvarchar(18) constraint FK_cardInfo_cardID references cardInfo(cardID) not null,
transType nvarchar(2) not null check(transType in('','֧ȡ')),
transMoney money check(transMoney>0) not null,
remark text
)
insert into userInfo(customerName,PID,telephone,address) values('','123456789012345','0716-78989783',''),('ɳ','421345678912345678','0478-44223333','ɳ'),('ɮ','321245678912345678','0478-44443333','é')
insert into cardInfo(balance,savingType,cardID,customerID) values(1000,'','1010 3576 1234 567',1),(1,'','1010 3576 1212 117',2),(1,'','1010 3576 1212 113',3)
update cardInfo set pass='611234' where customerID=1
insert into transInfo(cardID,transType,transMoney) values ('1010 3576 1234 567','֧ȡ',200)
update cardInfo set balance-=200 where customerID=1
insert into transInfo(cardID,transType,transMoney) values ('1010 3576 1212 117','',300)
update cardInfo set balance+=300 where customerID=2
update cardInfo set isReportLoss='' where customerID = 3
select * from cardInfo where DateDiff(dd,openDate,getdate())<=10
select max(transMoney)交易金额最大 from transInfo
select ܽ׽ = sum(transMoney) from transInfo  
select ֧ȡ׽ = sum(transMoney) from transInfo where transType = '֧ȡ'
select 뽻׽ = sum(transMoney) from transInfo where transType = ''
